<?php
declare (strict_types = 1);
namespace app\api\controller;
use app\models\Clb;
use app\models\Cljstmp;
use app\models\Cljsb;
use app\models\Yhb;
use think\facade\Db;

class Finance
{
	//查询车票
    public function index()
    {
        $item=Db::table('U_yhb')
		->where('yhqx','=','售票')
        ->field(['yhbh,yhxm,qsph,zzph,bfqs,bfzz'])
        ->select();
        $product = empty($item) ? array():$item->toArray();
        return json($item);
    }
	//修改票号
	public function xgph(){
		$data=Db::table('U_yhb')
		->where('yhqx','=','售票')
		->field(['yhbh','yhxm','qsph','zzph','bfqs','bfzz','ssgs'])
		->select();
		$product = empty($data) ? array():$data->toArray();
		return json($data);
	}
	//修改账号
	public function xgzh(){
		$data=Clb::field(['bbkh','czxm','cpzh','ssgs','acount'])
		->select();
		return json($data);
	}
	 //车辆信息查询
	public function clxx(){
		$data=Clb::alias('a')
		->join('U_xlb b','a.xlbh = b.xlbh')
		->field(
		['a.bbkh',
		'a.czxm',
		'a.cpzh',
		'a.ssgs',
		'a.zwgs',
		'a.clzt',
		'b.xlmc',
		'a.xlbh']
		)
		->select();
		return json($data);
	}
		//公司结算
	public function gsjs(){
		 
		Cljstmp::where('id','>=','0')->delete();
		//生成临时表数据
		$data=Db::table('U_cljsb')
		->where('rqsj','>=', '2018-01-01')
		->where('rqsj','<=','2018-03-01')
		->field(['
		MAX(bbkh) as bbkh,
		MAX(rqsj) as rqsj,
		SUM(gs) as gs,
		SUM(pjzs) as pjzs,
		SUM(tbzs) as tbzs,
		bcbh,
		ssgs,
		MAX(czy) as czy,
		cpzh,
		COUNT(cpzh) as jsds
		'])
		->group('cpzh,bcbh,rqsj,ssgs')
		->select();
		// return json($data);
		$data1 = $data->toArray();
		$a=Cljstmp::insertAll($data1);
		//每个公司的操作
		$data2=Cljstmp::alias('b')
		->join('U_clb a','a.cpzh=b.cpzh')
		->where('b.rqsj','>=', '2018-01-01')
		->where('b.rqsj','<=','2018-03-01')
		->where('b.ssgs','东站')
		->field(["
		a.cpzh,
		MAX(a.bbkh) as bbkh,
		MAX(a.czxm) as '车主姓名',
		MAX(a.acount) as '帐户',
		b.jsds as '结算单数',
		SUM(gs) as '检票张数',
		SUM(pjzs) as '客票金额'
		"])
		->group('a.cpzh')
		->select();
		// return json($data2);
		
		//总计
		$zj=Cljstmp::alias('b')
		->join('U_clb a','a.cpzh=b.cpzh')
		->where('b.rqsj','>=', '2018-01-01')
		->where('b.rqsj','<=','2018-03-01')
		->field(["
		MAX(a.acount) as '帐户',
		SUM(b.jsds) as '结算单数',
		SUM(gs) as '检票张数',
		SUM(pjzs) as '客票金额'
		"])
		->select();
		// return json($zj);
		return json([$data,$data2,$zj]);
	}
	//三公司结算
	public function sgsjs(){
		//删除车辆结算临时表中数据
		Cljstmp::where('id','>=','0')->delete();
		//生成临时表数据
		$data=Db::table('U_cljsb')
		->where('rqsj','>=', '2018-01-01')
		->where('rqsj','<=','2018-03-01')
		->where('ssgs','邯大三公司')
		->field(['
		MAX(bbkh) as bbkh,
		MAX(rqsj) as rqsj,
		SUM(gs) as gs,
		SUM(pjzs) as pjzs,
		SUM(tbzs) as tbzs,
		bcbh,
		ssgs,
		MAX(czy) as czy,
		cpzh,
		COUNT(cpzh) as jsds
		'])
		->group('cpzh,bcbh,rqsj,ssgs')
		->select();
		// return json($data);
		$data1 = $data->toArray();
		$a=Cljstmp::insertAll($data1);
		
		//查询车辆结算表中三公司
		// $data=Cljsb::where('rqsj','>=', '2018-01-01')
		// ->where('rqsj','<=','2018-03-01')
		// ->where('ssgs','东站')
		// ->field(['
		// MAX(bbkh) as bbkh,
		// MAX(rqsj) as rqsj,
		// SUM(gs) as gs,
		// SUM(pjzs) as pjzs,
		// SUM(tbzs) as tbzs,
		// MAX(bcbh) as bcbh,
		// MAX(ssgs) as ssgs,
		// MAX(czy) as czy,
		// COUNT(cpzh) as cpzh
		// '])
		// ->group('cpzh')
		// ->select();
		// return json($data);
		// $data1 = $data->toArray();
		// $a=Cljstmp::insertAll($data1);
		//三公司结算
		$data2=Cljstmp::alias('b')
		->join('U_clb a','a.cpzh=b.cpzh')
		->where('b.rqsj','>=', '2018-01-01')
		->where('b.rqsj','<=','2018-03-01')
		->where('a.ssgs','邯大三公司')
		->field(["
		a.cpzh,
		MAX(a.bbkh) as bbkh,
		MAX(a.czxm) as '车主姓名',
		MAX(a.acount) as '帐户',
		SUM(b.jsds) as '结算单数',
		SUM(gs) as '检票张数',
		SUM(pjzs) as '客票金额'
		"])
		->group('a.cpzh')
		->select();
		// return json($data2);
		//总计
		$zj=Cljstmp::alias('b')
		->join('U_clb a','a.cpzh=b.cpzh')
		->where('b.rqsj','>=', '2018-01-01')
		->where('b.rqsj','<=','2018-03-01')
		->where('a.ssgs','邯大三公司')
		->field(["
		MAX(a.acount) as '帐户',
		SUM(b.jsds) as '结算单数',
		SUM(gs) as '检票张数',
		SUM(pjzs) as '客票金额'
		"])
		->select();
		// return json($zj);
		return json([$data,$data2,$zj]);
		
	 }
	public function dzxljs(){
		//删除车辆结算临时表中数据
		Cljstmp::where('id','>=','0')->delete();
		//生成临时表数据
		$data=Db::table('U_cljsb')
		->where('rqsj','>=','2018-01-01')
		->where('rqsj','<=','2018-03-01')
		->field(['
		MAX(bbkh) as bbkh,
		MAX(rqsj) as rqsj,
		SUM(gs) as gs,
		SUM(pjzs) as pjzs,
		SUM(tbzs) as tbzs,
		bcbh,
		ssgs,
		MAX(czy) as czy,
		cpzh,
		COUNT(cpzh) as jsds
		'])
		->group('cpzh,bcbh,rqsj,ssgs')
		->select();
		// return json($data);
		$data1 = $data->toArray();
		$a=Cljstmp::insertAll($data1);
		//东站线路结算
		$xl=Cljstmp::alias('a')
		->join('U_clb b','a.cpzh=b.cpzh')
		->join('U_xlb c','b.xlbh=c.xlbh')
		->where('c.ssgs','东站')
		->where('a.rqsj','>=','2018-01-01')
		->where('a.rqsj','<=','2018-03-01')
		->field(["
		 c.xlmc as '线路名称',
		 a.cpzh,
		 MAX(a.bbkh) as bbkh,
		 SUM(a.jsds) as '结算单数',
		 SUM(gs) as '检票数量',
		 SUM(pjzs) as '总票价'
		"])
		->group('a.cpzh,c.xlmc')
		->order('c.xlmc,a.cpzh')
		->select();
		return json($xl);
	 }
	 //公司结算月报
	public function gsjsyb(){
		//删除车辆结算临时表中数据
		Cljstmp::where('id','>=','0')->delete();
		//生成临时表数据
		$data=Db::table('U_cljsb')
		->where('rqsj','>=','2018-01-01')
		->where('rqsj','<=','2018-03-01')
		->where('ssgs','东站')
		->field(['
		MAX(bbkh) as bbkh,
		MAX(rqsj) as rqsj,
		SUM(gs) as gs,
		SUM(pjzs) as pjzs,
		SUM(tbzs) as tbzs,
		bcbh,
		ssgs,
		MAX(czy) as czy,
		cpzh,
		COUNT(cpzh) as jsds
		'])
		->group('cpzh,bcbh,rqsj,ssgs')
		->select();
		// return json($data);
		$data1 = $data->toArray();
		$a=Cljstmp::insertAll($data1);
		//所有公司月报
		$yb=Cljstmp::alias('b')
		->join('U_clb a','a.cpzh=b.cpzh')
		->where('b.rqsj','>=', '2018-01-01')
		->where('b.rqsj','<=','2018-03-01')
		->field(["
		a.ssgs as '所属公司',
		SUM(b.jsds) as '结算单数',
		SUM(gs) as '检票张数',
		SUM(pjzs) as '客票金额'
		"])
		->group('a.ssgs')
		->select();
		//总计*所有公司
		$zj=Cljstmp::alias('b')
		->join('U_clb a','a.cpzh=b.cpzh')
		->where('b.rqsj','>=', '2018-01-01')
		->where('b.rqsj','<=','2018-03-01')
		->field(["
		SUM(b.jsds) as '结算单数',
		SUM(gs) as '检票张数',
		SUM(pjzs) as '客票金额'
		"])
		->select();
		return json([$yb,$zj]);
	}
	public function yyqk(){
		//东站合计
		$dz=Yhb::alias('a')
		->join('U_cpbbak b','a.yhbh=b.spy')
		->where('a.yhgroup','like','第一组|第二组')
		->where('b.rqsj','>=','2018-01-01')
		->where('b.rqsj','<=','2018-03-01')
		->field(["
		count(b.cpbh) as '售票数',
		sum(b.pj+b.tbpj) as '总票款',
		sum(b.tbpj) as '微机费',
		sum(case when b.sfjp like '已检' then 1 else 0 end) as '检票数',
		sum(case when b.sfjp like '已检' then b.pj+b.tbpj else 0 end )  as '检票款',
		sum(case when b.cpzt like '退票' then 1 else 0 end) as '退票数',
		sum(case when b.cpzt like '退票' then -b.sxf else 0 end)  as '退票款',
		sum(case when b.cpzt like '正常' and b.sfjp like '未检' then 1 else 0 end) as '长短票数',
		sum(case when b.cpzt like '正常' and b.sfjp like '未检' then b.pj+b.tbpj else 0 end) as '长短票款',
		sum(case when b.cpzt like '退票' then b.pj+b.tbpj-b.sxf else 0 end) as '手续费',
		sum(pj+tbpj-sxf) as 合计
		"])
		->select();
		// return $dz;
		//三公司合计
		$sgs=Yhb::alias('a')
		->join('U_cpbbak b','a.yhbh=b.spy')
		->where('a.yhgroup','like','三公司')
		->where('b.rqsj','>=','2018-01-01')
		->where('b.rqsj','<=','2018-03-01')
		->field(["
		count(b.cpbh) as '售票数',
		sum(b.pj+b.tbpj) as '总票款',
		sum(b.tbpj) as '微机费',
		sum(case when b.sfjp like '已检' then 1 else 0 end) as '检票数',
		sum(case when b.sfjp like '已检' then b.pj+b.tbpj else 0 end )  as '检票款',
		sum(case when b.cpzt like '退票' then 1 else 0 end) as '退票数',
		sum(case when b.cpzt like '退票' then -sxf else 0 end)  as '退票款',
		sum(case when b.cpzt like '正常' and b.sfjp like '未检' then 1 else 0 end) as '长短票数',
		sum(case when b.cpzt like '正常' and b.sfjp like '未检' then b.pj+b.tbpj else 0 end) as '长短票款',
		sum(case when b.cpzt like '退票' then b.pj+b.tbpj-b.sxf else 0 end) as '手续费',
		sum(b.pj+b.tbpj-b.sxf) as 合计
		"])
		->select();
		// return $sgs;
		//全站合计
		$zj=Yhb::alias('a')
		->join('U_cpbbak b','a.yhbh=b.spy')
		->where('b.rqsj','>=','2018-01-01')
		->where('b.rqsj','<=','2018-03-01')
		->field(["
		count(b.cpbh) as '售票数',
		sum(b.pj+b.tbpj) as '总票款',
		sum(tbpj) as '微机费',
		sum(case when b.sfjp like '已检' then 1 else 0 end) as '检票数',
		sum(case when b.sfjp like '已检' then b.pj+b.tbpj else 0 end )  as '检票款',
		sum(case when b.cpzt like '退票' then 1 else 0 end) as '退票数',
		sum(case when b.cpzt like '退票' then -b.sxf else 0 end)  as '退票款',
		sum(case when b.cpzt like '正常' and b.sfjp like '未检' then 1 else 0 end) as '长短票数',
		sum(case when b.cpzt like '正常' and b.sfjp like '未检' then b.pj+b.tbpj else 0 end) as '长短票款',
		sum(case when b.cpzt like '退票' then b.pj+b.tbpj-b.sxf else 0 end) as '手续费',
		sum(pj+tbpj-sxf) as 合计
		"])
		->select();
		return json([$dz,$sgs,$zj]);
	}
	public function spqkcx(){
		//东站第一组
		// $dzz1=Db::query("
		// SELECT
		// 			max( U_yhb.yhxm ) AS yhxm ,
		// 			max(U_yhb.yhbh) AS yhbh,
		// 			(
		// 			SELECT
		// 				count( cpbh )
		// 			FROM
		// 				U_cpb_fp 
		// 			WHERE
		// 				U_cpb_fp.spy = U_yhb.yhbh 
		// 			AND 
		// 				U_cpb_fp.rqsj >= '2018-01-01' 
		// 			AND 
		// 			U_cpb_fp.rqsj <= '2018-03-01' ) AS '废票数',
		// 			(
		// 			SELECT
		// 				max( U_cpbbak.cpbh ) 
		// 			FROM
		// 				U_cpbbak 
		// 			WHERE
		// 				U_cpbbak.spy = U_yhb.yhbh 
		// 			AND 
		// 				U_cpbbak.rqsj >= '2018-01-01'  
		// 			AND 
		// 			U_cpbbak.rqsj <= '2018-03-01') AS '止票号',
		// 			count( U_cpbbak.cpbh ) AS '售票数',
		// 			sum( U_cpbbak.pj + U_cpbbak.tbpj ) AS '总票款',
		// 			sum( U_cpbbak.tbpj ) AS 微机费,
		// 			sum( CASE U_cpbbak.sfjp WHEN '已检' THEN 1 ELSE 0 END ) AS '检票数',
		// 			sum( CASE U_cpbbak.sfjp WHEN '已检' THEN U_cpbbak.pj + U_cpbbak.tbpj ELSE 0 END ) AS '检票款',
		// 			0 AS '退票数',
		// 			0 AS '退票款',
		// 			0 AS '长短票数',
		// 			0 AS '长短票款',
		// 			0 AS '手续费',
		// 			sum( U_cpbbak.pj + U_cpbbak.tbpj ) AS '合计' 
		// 		FROM
		// 			U_yhb,
		// 			U_cpbbak
		// 		WHERE
		// 		U_yhb.yhbh=	U_cpbbak.spy
		// 		AND
		// 			U_yhb.yhgroup = '第一组' 
		// 		AND 
		// 			 U_cpbbak.rqsj >= '2018-01-01'
		// 		AND 
		// 			 U_cpbbak.rqsj <= '2018-03-01'
		// 		GROUP BY
		// 			U_yhb.yhbh
		// ");
		//   return json($dzz1);
		  
		//东站一组小计
		// $dzz1=Yhb::alias('a')
		// ->join('U_cpbbak b','a.yhbh=b.spy')
		// ->where('a.yhgroup','=','第一组')
		// ->where('b.rqsj','>=','2018-01-11')
		// ->where('b.rqsj','<=','2018-02-11')
		// ->field(["
		//  '一组小计' as '姓名',
		//  0 as '废票数',
		//  '' as '止票号',
		//  count(b.cpbh) as '售票数',
		//  sum(b.pj+b.tbpj) as '总票款',
		//  sum(b.tbpj) as '微机费',
		//  sum(case when b.sfjp like '已检' then 1 else 0 end) as '检票数',
		//  sum(case when b.sfjp like '已检' then b.pj+b.tbpj else 0 end ) as '检票款',
		//  0 as '退票数',
		//  0 as '退票款',
		//  sum(case  when b.cpzt like '正常' and b.sfjp='未检' then 1 else 0 end) as '长短票数',
		//  sum(case  when b.cpzt='正常' and b.sfjp='未检' then b.pj+b.tbpj else 0 end) as '长短票款',
		//  0 as '手续费',
		//  sum(b.pj+b.tbpj-b.sxf) as '合计'
		// "])
		// ->select();
		// return json($dzz1);
		// //东站第二组
		// $dzz2=Db::query("
		// SELECT
		// 			max( U_yhb.yhxm ) AS yhxm ,
		// 			max(U_yhb.yhbh) AS yhbh,
		// 			(
		// 			SELECT
		// 				count( cpbh )
		// 			FROM
		// 				U_cpb_fp 
		// 			WHERE
		// 				U_cpb_fp.spy = U_yhb.yhbh 
		// 			AND 
		// 				U_cpb_fp.rqsj >= '2018-01-01' 
		// 			AND 
		// 			U_cpb_fp.rqsj <= '2018-03-01' ) AS '废票数',
		// 			(
		// 			SELECT
		// 				max( U_cpbbak.cpbh ) 
		// 			FROM
		// 				U_cpbbak 
		// 			WHERE
		// 				U_cpbbak.spy = U_yhb.yhbh 
		// 			AND 
		// 				U_cpbbak.rqsj >= '2018-01-01'  
		// 			AND 
		// 			U_cpbbak.rqsj <= '2018-03-01') AS '止票号',
		// 			count( U_cpbbak.cpbh ) AS '售票数',
		// 			sum( U_cpbbak.pj + U_cpbbak.tbpj ) AS '总票款',
		// 			sum( U_cpbbak.tbpj ) AS 微机费,
		// 			sum( CASE U_cpbbak.sfjp WHEN '已检' THEN 1 ELSE 0 END ) AS '检票数',
		// 			sum( CASE U_cpbbak.sfjp WHEN '已检' THEN U_cpbbak.pj + U_cpbbak.tbpj ELSE 0 END ) AS '检票款',
		// 			0 AS '退票数',
		// 			0 AS '退票款',
		// 			0 AS '长短票数',
		// 			0 AS '长短票款',
		// 			0 AS '手续费',
		// 			sum( U_cpbbak.pj + U_cpbbak.tbpj ) AS '合计' 
		// 		FROM
		// 			U_yhb,
		// 			U_cpbbak
		// 		WHERE
		// 		U_yhb.yhbh=	U_cpbbak.spy
		// 		AND
		// 			U_yhb.yhgroup = '第二组' 
		// 		AND 
		// 			 U_cpbbak.rqsj >= '2018-01-01'
		// 		AND 
		// 			 U_cpbbak.rqsj <= '2018-03-01'
		// 		GROUP BY
		// 			U_yhb.yhbh
		// ");
		//   return json($dzz2);
		// //东站二组小计
		// $dzz2=Yhb::alias('a')
		// ->join('U_cpbbak b','a.yhbh=b.spy')
		// ->where('a.yhgroup','like','第二组')
		// ->where('b.rqsj','>=','2018-01-11')
		// ->where('b.rqsj','<=','2018-03-11')
		// ->field(["
		//  '二组小计' as '姓名',
		//  0 as '废票数',
		//  '' as '止票号',
		//  count(b.cpbh) as '售票数',
		//  sum(b.pj+b.tbpj) as '总票款',
		//  sum(b.tbpj) as '微机费',
		//  sum(case when b.sfjp like '已检' then 1 else 0 end) as '检票数',
		//  sum(case when b.sfjp like '已检' then b.pj+b.tbpj else 0 end ) as '检票款',
		//  0 as '退票数',
		//  0 as '退票款',
		//  sum(case  when b.cpzt like '正常' and b.sfjp='未检' then 1 else 0 end) as '长短票数',
		//  sum(case  when b.cpzt='正常' and b.sfjp='未检' then b.pj+b.tbpj else 0 end) as '长短票款',
		//  0 as '手续费',
		//  sum(b.pj+b.tbpj-b.sxf) as '合计'
		// "])
		// ->select();
		// return json($dzz2);
		// //东站退票（数据库没有数据)
		// $dztp=Yhb::alias('a')
		// ->join('U_cpbbak b','a.yhbh=b.spy')
		// ->where('a.yhgroup','like','第一组|第二组')
		// ->where('b.rqsj','>=','2016-01-01')
		// ->where('b.rqsj','<=','2019-03-01')
		// ->field(["
		// max(a.yhxm) as 姓名,
		// 0 as '废票数',
		// '' as '止票号',
		// 0 as '售票数',
		// 0 as '总票款',
		// 0 as '微机费',
		// 0 as '检票数',
		// 0 as '检票款',
		// count(b.cpbh) as '退票数',
		// sum(-b.sxf) as '退票款',
		// 0 as '长短票数',
		// 0 as '长短票款',
		// sum(b.pj+b.tbpj-b.sxf) as '手续费',
		// 0 as '合计'
		// "])
		// ->group('a.yhbh')
		// ->select();
		// return json($dztp);
		// //东站总计
		// $dzzj=Yhb::alias('a')
		// ->join('U_cpbbak b','a.yhbh=b.spy')
		// ->where('a.yhgroup','like',['第一组','第二组'],'OR')
		// ->where('b.rqsj','>=','2018-01-01')
		// ->where('b.rqsj','<=','2018-03-01')
		// ->field(["
		// '东站合计' as '姓名',
		// 0 as '废票数',
		// '' as '止票号',
		// count(b.cpbh) as '售票数',
		// sum(b.pj+b.tbpj) as '总票款',
		// sum(case when b.sfjp like '已检' then b.tbpj else 0 end ) as '微机费',
		// sum(case when b.sfjp like '已检' then 1 else 0 end) as '检票数',
		// sum(case when b.sfjp like '已检' then b.pj+b.tbpj else 0 end ) as '检票款',
		// sum(case when b.cpzt like '退票' then 1 else 0 end) as '退票数',
		// sum(case when b.cpzt like '退票' then -b.sxf else 0 end)  as '退票款',
		// sum(case when b.cpzt like '正常' and b.sfjp like '未检' then 1 else 0 end) as '长短票数',
		// sum(case when b.cpzt like '正常' and b.sfjp like '未检' then b.pj+b.tbpj else 0 end) as '长短票款',
		// sum(case when b.cpzt like '退票' then b.pj+b.tbpj-b.sxf else 0 end) as '手续费',
		// sum(pj+tbpj-sxf) as 合计
		// "])
		// ->select();
		// return json($dzzj);
		//s三公司
		// $sgs1=Db::query("
		// SELECT
		// 			max( U_yhb.yhxm ) AS yhxm ,
		// 			max(U_yhb.yhbh) AS yhbh,
		// 			(
		// 			SELECT
		// 				count( cpbh )
		// 			FROM
		// 				U_cpb_fp 
		// 			WHERE
		// 				U_cpb_fp.spy = U_yhb.yhbh 
		// 			AND 
		// 				U_cpb_fp.rqsj >= '2018-01-01' 
		// 			AND 
		// 			U_cpb_fp.rqsj <= '2018-03-01' ) AS '废票数',
		// 			(
		// 			SELECT
		// 				max( U_cpbbak.cpbh ) 
		// 			FROM
		// 				U_cpbbak 
		// 			WHERE
		// 				U_cpbbak.spy = U_yhb.yhbh 
		// 			AND 
		// 				U_cpbbak.rqsj >= '2018-01-01'  
		// 			AND 
		// 			U_cpbbak.rqsj <= '2018-03-01') AS '止票号',
		// 			count( U_cpbbak.cpbh ) AS '售票数',
		// 			sum( U_cpbbak.pj + U_cpbbak.tbpj ) AS '总票款',
		// 			sum( U_cpbbak.tbpj ) AS 微机费,
		// 			sum( CASE U_cpbbak.sfjp WHEN '已检' THEN 1 ELSE 0 END ) AS '检票数',
		// 			sum( CASE U_cpbbak.sfjp WHEN '已检' THEN U_cpbbak.pj + U_cpbbak.tbpj ELSE 0 END ) AS '检票款',
		// 			0 AS '退票数',
		// 			0 AS '退票款',
		// 			0 AS '长短票数',
		// 			0 AS '长短票款',
		// 			0 AS '手续费',
		// 			sum( U_cpbbak.pj + U_cpbbak.tbpj ) AS '合计' 
		// 		FROM
		// 			U_yhb,
		// 			U_cpbbak
		// 		WHERE
		// 		U_yhb.yhbh=	U_cpbbak.spy
		// 		AND
		// 			U_yhb.yhgroup = '三公司' 
		// 		AND 
		// 			 U_cpbbak.rqsj >= '2018-01-01'
		// 		AND 
		// 			 U_cpbbak.rqsj <= '2018-03-01'
		// 		GROUP BY
		// 			U_yhb.yhbh
		// ");
		// return json($sgs1);
		// //三公司合计
		// $sgszj=Yhb::alias('a')
		// ->join('U_cpbbak b','a.yhbh=b.spy')
		// ->where('a.yhgroup','like','三公司')
		// ->where('b.rqsj','>=','2018-01-01')
		// ->where('b.rqsj','<=','2018-03-01')
		// ->field(["
		// '三公司合计' as '姓名',
		// 0 as '废票数',
		// '' as '止票号',
		// count(b.cpbh) as '售票数',
		// sum(b.pj+b.tbpj) as '总票款',
		// sum(case when b.sfjp like '已检' then b.tbpj else 0 end ) as '微机费',
		// sum(case when b.sfjp like '已检' then 1 else 0 end) as '检票数',
		// sum(case when b.sfjp like '已检' then b.pj+b.tbpj else 0 end ) as '检票款',
		// sum(case when b.cpzt like '退票' then 1 else 0 end) as '退票数',
		// sum(case when b.cpzt like '退票' then -b.sxf else 0 end)  as '退票款',
		// sum(case when b.cpzt like '正常' and b.sfjp like '未检' then 1 else 0 end) as '长短票数',
		// sum(case when b.cpzt like '正常' and b.sfjp like '未检' then b.pj+b.tbpj else 0 end) as '长短票款',
		// sum(case when b.cpzt like '退票' then b.pj+b.tbpj-b.sxf else 0 end) as '手续费',
		// sum(pj+tbpj-sxf) as 合计
		// "])
		// ->select();
		// return json($sgszj);
		// //全部查询
		// //东站小组
		// $dzxz=Db::query("
		// SELECT
		// 			max( U_yhb.yhxm ) AS yhxm ,
		// 			max(U_yhb.yhbh) AS yhbh,
		// 			(
		// 			SELECT
		// 				count( cpbh )
		// 			FROM
		// 				U_cpb_fp 
		// 			WHERE
		// 				U_cpb_fp.spy = U_yhb.yhbh 
		// 			AND 
		// 				U_cpb_fp.rqsj >= '2018-01-01' 
		// 			AND 
		// 			U_cpb_fp.rqsj <= '2018-03-01' ) AS '废票数',
		// 			(
		// 			SELECT
		// 				max( U_cpbbak.cpbh ) 
		// 			FROM
		// 				U_cpbbak 
		// 			WHERE
		// 				U_cpbbak.spy = U_yhb.yhbh 
		// 			AND 
		// 				U_cpbbak.rqsj >= '2018-01-01'  
		// 			AND 
		// 			U_cpbbak.rqsj <= '2018-03-01') AS '止票号',
		// 			count( U_cpbbak.cpbh ) AS '售票数',
		// 			sum( U_cpbbak.pj + U_cpbbak.tbpj ) AS '总票款',
		// 			sum( U_cpbbak.tbpj ) AS 微机费,
		// 			sum( CASE U_cpbbak.sfjp WHEN '已检' THEN 1 ELSE 0 END ) AS '检票数',
		// 			sum( CASE U_cpbbak.sfjp WHEN '已检' THEN U_cpbbak.pj + U_cpbbak.tbpj ELSE 0 END ) AS '检票款',
		// 			0 AS '退票数',
		// 			0 AS '退票款',
		// 			0 AS '长短票数',
		// 			0 AS '长短票款',
		// 			0 AS '手续费',
		// 			sum( U_cpbbak.pj + U_cpbbak.tbpj ) AS '合计' 
		// 		FROM
		// 			U_yhb,
		// 			U_cpbbak
		// 		WHERE
		// 		U_yhb.yhbh=	U_cpbbak.spy
		// 		AND(
		// 			U_yhb.yhgroup like '第一组'
		// 		OR  
		// 			U_yhb.yhgroup like '第二组'
		// 			)
		// 		AND 
		// 			 U_cpbbak.rqsj >= '2018-01-01'
		// 		AND 
		// 			 U_cpbbak.rqsj <= '2018-03-01'
		// 		GROUP BY
		// 			U_yhb.yhbh
		// ");
		// return json($dzxz);
		//东站退票
		//return $dztp;
		//东站合计
		//return $dzzj;
		//三公司
		//return $sgs1;
		//三公司退票
		// $sgstp=Yhb::alias('a')
		// ->join('U_cpbbak b','a.yhbh=b.spy')
		// ->where('a.yhgroup','=','三公司')
		// ->where('b.rqsj','>=','2016-01-01')
		// ->where('b.rqsj','<=','2019-03-01')
		// ->field(["
		// max(a.yhxm) as 姓名,
		// 0 as '废票数',
		// '' as '止票号',
		// 0 as '售票数',
		// 0 as '总票款',
		// 0 as '微机费',
		// 0 as '检票数',
		// 0 as '检票款',
		// count(b.cpbh) as '退票数',
		// sum(-b.sxf) as '退票款',
		// 0 as '长短票数',
		// 0 as '长短票款',
		// sum(b.pj+b.tbpj-b.sxf) as '手续费',
		// 0 as '合计'
		// "])
		// ->group('a.yhbh')
		// ->select();
		// return json($sgstp);
		//三公司合计
		//return $sgszj;
		// //全站合计
		// $qzhj=Yhb::alias('a')
		// ->join('U_cpbbak b','a.yhbh=b.spy')
		// ->where('a.yhgroup','like',['第一组','第二组','三公司'],'OR')
		// ->where('b.rqsj','>=','2018-01-01')
		// ->where('b.rqsj','<=','2018-03-01')
		// ->field(["
		// '东站合计' as '姓名',
		// 0 as '废票数',
		// '' as '止票号',
		// count(b.cpbh) as '售票数',
		// sum(b.pj+b.tbpj) as '总票款',
		// sum(case when b.sfjp like '已检' then b.tbpj else 0 end ) as '微机费',
		// sum(case when b.sfjp like '已检' then 1 else 0 end) as '检票数',
		// sum(case when b.sfjp like '已检' then b.pj+b.tbpj else 0 end ) as '检票款',
		// sum(case when b.cpzt like '退票' then 1 else 0 end) as '退票数',
		// sum(case when b.cpzt like '退票' then -b.sxf else 0 end)  as '退票款',
		// sum(case when b.cpzt like '正常' and b.sfjp like '未检' then 1 else 0 end) as '长短票数',
		// sum(case when b.cpzt like '正常' and b.sfjp like '未检' then b.pj+b.tbpj else 0 end) as '长短票款',
		// sum(case when b.cpzt like '退票' then b.pj+b.tbpj-b.sxf else 0 end) as '手续费',
		// sum(pj+tbpj-sxf) as 合计
		// "])
		// ->select();
		// return json($qzhj);
	}
	
		
}
